﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Globalization;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class frm_Add_F4SerialInput : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;

        private DataSet ds;
        private DataViewManager dsView;

        private string year_letter;
        private string month_letter;
        private string first_letter;

        private string GHR_year_letter;

        private string SerialLetter;
        private int SerialDigit;

        private string[] Alphabet;

        private Dictionary<string, string> year_letters;
        private Dictionary<string, string> month_letters;

        public frm_Add_F4SerialInput()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);
            this.ds = new DataSet("F4Plan");

            this.year_letters = new Dictionary<string, string>(){{"09", "A9"}, {"10", "B0"}, {"11", "B1"}, {"12", "B2"},
                {"13", "B3"}, {"14", "B4"}, {"15", "B5"}, {"16", "B6"}, {"17", "B7"}, {"18", "B8"}, {"19", "B9"}, {"20", "C0"}};
            this.month_letters = new Dictionary<string, string>()
                {{"01", "A"}, {"02", "B"}, {"03", "C"}, {"04", "D"}, {"05", "E"}, {"06", "F"},
                {"07", "G"}, {"08", "H"}, {"09", "J"}, {"10", "K"}, {"11", "L"}, {"12", "M"}};
            
            this.year_letter = null;
            this.month_letter = null;
            this.first_letter = null;

            this.GHR_year_letter = null;
            this.SerialDigit = 0;
            this.SerialLetter = null;

            this.Alphabet = new string[24] { "A", "B", "C", "D", "E", "F", "G", "H", "J", "K",
                "L", "M", "N", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };

            LoadOrderList();
            this.deadLine.TextChanged += deadLine_TextChanged;
            this.quantityValue.TextChanged += quantityValue_TextChanged;
            this.compID.TextChanged += compID_TextChanged;

            this.KeyPress += frm_Add_F4SerialInput_KeyPress;

            this.orderNo.LostFocus += orderNo_LostFocus;

        }

        void orderNo_LostFocus(object sender, EventArgs e)
        {
            if (this.orderNo.FindString(this.orderNo.Text.Trim()) > 0)
            {
                this.orderNo.SelectedIndex = this.orderNo.FindString(this.orderNo.Text.Trim());
            }
        }

        void frm_Add_F4SerialInput_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == Convert.ToChar(Keys.Enter)) 
            {
                if (this.buttonAdd.Enabled == false)
                {
                    this.buttonCalculate.Focus();
                    CalculateSerialNo();
                    this.buttonAdd.Focus();
                }
                else
                {
                    this.buttonAdd.Focus();
                    InsertSerialNo();
                }
            }
            //throw new NotImplementedException();
        }

        void compID_TextChanged(object sender, EventArgs e)
        {
            //CalculateSerialNo();
        }

        void quantityValue_TextChanged(object sender, EventArgs e)
        {
            //CalculateSerialNo(); 
        }

        void deadLine_TextChanged(object sender, EventArgs e)
        {            
            if (this.deadLine.Text.Length == 8)
            {
                this.monthText.Text = this.deadLine.Text.Substring(4, 2);
                this.yearText.Text = this.deadLine.Text.Substring(2, 2);         
            } 
        }
                        
        ////////////////////////////////
        /// Load Order List
        ///////////////////////////////
        private void LoadOrderList()
        {
            try
            {
                masterConnection.Open();
                SqlCommand cmd_sql = masterConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT * FROM OPENQUERY(AS400_MPFLIB, 'SELECT MPOR00.ORODNO AS ORDERNO,"
                    + " NEKO00.KOKENO AS PLANNO, MPOR00.ORHNBN AS PARTNO, MPOR00.ORHTSU AS QUANTITY,"
                    + " MPOR00.ORHTBI AS COMMANDDATE, MPOR80.O8NOK1 AS DEADLINE"
                    + " FROM MPOR00	LEFT JOIN"
                    + " MPIT00 ON MPIT00.ITHNBN = MPOR00.ORHNBN"
                    + "	LEFT JOIN NEKO00 ON NEKO00.KOODNO = MPOR00.ORODNO"
                    + " LEFT JOIN MPOR80 ON MPOR00.ORODNO = MPOR80.O8ODNO"
                    + " WHERE SUBSTRING(MPIT00.ITFILL,16,4)=''0232'' AND MPOR00.ORHNTP=''M'' AND MPOR00.ORHNBN NOT LIKE ''CSZ0-%''')";
                cmd_sql.CommandTimeout = 10000;
                SqlDataAdapter da1 = new SqlDataAdapter();
                da1.SelectCommand = cmd_sql;                
                da1.TableMappings.Add("Table", "F4PlanTable");
                da1.Fill(ds);
                dsView = ds.DefaultViewManager;                
                cmd_sql.Dispose();
                masterConnection.Close();
                
                this.orderNo.DataSource = dsView;
                this.orderNo.DisplayMember = "F4PlanTable.ORDERNO";
                this.orderNo.ValueMember = "F4PlanTable.ORDERNO";                

                this.planNo.DataBindings.Add("Text", dsView, "F4PlanTable.PLANNO");
                this.compID.DataBindings.Add("Text", dsView, "F4PlanTable.PARTNO");
                this.quantityValue.DataBindings.Add("Text", dsView, "F4PlanTable.QUANTITY");
                this.deadLine.DataBindings.Add("Text", dsView, "F4PlanTable.DEADLINE");

                myConnection.Open();
                cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT * FROM [231_Customer]";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["CustomerID"].ToString();
                    item.Value = dataReader["CustomerID"].ToString();
                    this.customerID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }
                MessageBox.Show(ex.Message);                
            }
        }

        ///////////////////////////
        /// Order No change
        ///////////////////////////
        private void orderNo_SelectedIndexChanged_1(object sender, EventArgs e)
        {
            this.firstSerial.Text = null;
            this.lastSerial.Text = null;            
        }

        ///////////////////////////////
        /// Calculate Serial No
        ///////////////////////////////
        private void CalculateSerialNo()
        {
            // Convert Year letter and Month letter            
            if (year_letters.ContainsKey(this.yearText.Text))
            {
                year_letter = year_letters[this.yearText.Text];
            }            

            if (month_letters.ContainsKey(this.monthText.Text))
            {
                month_letter = month_letters[this.monthText.Text];
            }           
                            
            // Comp loại GHR
            if (this.compID.Text.Substring(0, 3).ToUpper() == "GHR")
            {
                this.first_letter = "V";
                int year = 0;
                int month = 0;

                if ((Int32.TryParse(year_letter.Substring(1, 1), out year)) && (Int32.TryParse(this.monthText.Text, out month)))
                {
                    GHR_year_letter = "1" + (year + ((month + 9) / 12) - 1).ToString();

                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT TOP (10) CompID, SerialNo"
                            + " FROM [231_RobotOrderDetail] WHERE (LEFT(CompID, 5) LIKE N'" + this.compID.Text.Substring(0, 5).ToUpper() + "')"
                            + " ORDER BY SUBSTRING(SerialNo, 4, 4) DESC";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            this.lastSerial.Text = dataReader["SerialNo"].ToString();
                        }
                        else
                        {
                            this.lastSerial.Text = first_letter + GHR_year_letter + "0000";
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        //error = "Cannot get Comp list";
                    }

                    SerialDigit = GetGHRSerialDigit(this.lastSerial.Text);

                    this.firstSerial.Text = first_letter + GHR_year_letter + GetGHRSerialLetter(SerialDigit + 1)
                        + ((SerialDigit + 1) % 1000).ToString("D3");

                }
                else
                {
                    MessageBox.Show("Check year & month");
                }
            }
            // Comp không phải GHR
            else
            {
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT Serial FROM [231_Comp] WHERE CompID='" + this.compID.Text + "'";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {
                        first_letter = dataReader["Serial"].ToString();
                    }
                    else
                    {
                        MessageBox.Show("No registered CompID");
                        // Create new Serial for Comp
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //error = "Cannot get Comp list";
                }

                string[] array1 = { "A", "B", "D", "E", "F", "H", "I", "J", "K", "L", "M", "N", "O", "P",
                                      "Q", "R", "S", "T", "U", "W", "Y", "Z" };
                string[] array2 = { "VQ", "VW", "VY", "VR", "VS", "VU", "VH", "VE", "AP", "AN", "AU" };
                string[] array3 = { "RT-AF", "RT-AR", "RT-BF", "RT-BR", "RT-CF", "RT-CR" };
                string[] array4 = { "RT" };

                if (array1.Contains(first_letter))
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT CompID, SerialNo"
                            + " FROM [231_RobotOrderDetail]"
                            + " WHERE (LEFT(SerialNo, 1)='" + first_letter + "')"
                            + " AND (SUBSTRING(SerialNo, 2, 2) = '" + year_letter + "')"
                            + " AND (SUBSTRING(SerialNo, 4, 1) = '" + month_letter + "')"
                            + " ORDER BY SerialNo DESC";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            lastSerial.Text = dataReader["SerialNo"].ToString();

                            if (lastSerial.Text.Substring(lastSerial.Text.Length - 1, 1) == "f")
                            {
                                lastSerial.Text = lastSerial.Text.Substring(0, 7);
                            }
                        }
                        else
                        {
                            lastSerial.Text = first_letter + year_letter + month_letter + "000";
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        //error = "Cannot get Comp list";
                    }

                    SerialDigit = GetSerialDigit(lastSerial.Text, 1);
                }

                if (array2.Contains(first_letter))
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT CompID, SerialNo"
                            + " FROM [231_RobotOrderDetail]"
                            + " WHERE (LEFT(SerialNo, 2)='" + first_letter + "')"
                            + " AND (SUBSTRING(SerialNo, 3, 2) = '" + year_letter + "')"
                            + " AND (SUBSTRING(SerialNo, 5, 1) = '" + month_letter + "')"
                            + " ORDER BY SerialNo DESC";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            lastSerial.Text = dataReader["SerialNo"].ToString();
                        }
                        else
                        {
                            lastSerial.Text = first_letter + year_letter + month_letter + "000";
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        //error = "Cannot get Comp list";
                    }

                    SerialDigit = GetSerialDigit(lastSerial.Text, 2);
                }

                if (array3.Contains(first_letter))
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT CompID, SerialNo"
                            + " FROM [231_RobotOrderDetail]"
                            + " WHERE (LEFT(SerialNo, 2)='" + first_letter.Substring(0, 2) + "')"
                            + " AND (SUBSTRING(SerialNo, 7, 1) = '" + year_letter.Substring(1, 1) + "')"
                            + " AND (SUBSTRING(SerialNo, 9, 1) = '" + month_letter + "')"
                            + " ORDER BY RIGHT(SerialNo, 6) DESC";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            lastSerial.Text = dataReader["SerialNo"].ToString();
                        }
                        else
                        {
                            lastSerial.Text = first_letter + "-" + year_letter.Substring(1, 1)
                                + "-" + month_letter + "-" + "00";
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        //error = "Cannot get Comp list";
                    }

                    SerialDigit = GetSerialDigit(lastSerial.Text, 3);
                }

                if (array4.Contains(first_letter))
                {
                    Dictionary<string, string> Month_Letters = new Dictionary<string, string>()
                        {{"01", "1"}, {"02", "2"}, {"03", "3"}, {"04", "4"}, {"05", "5"}, {"06", "6"}, {"07", "7"},
                        {"08", "8"}, {"09", "9"}, {"10", "X"}, {"11", "Y"}, {"12", "Z"}};
                    

                    if (Month_Letters.ContainsKey(this.monthText.Text))
                    {
                        month_letter = Month_Letters[this.monthText.Text];
                    }

                    year_letter = this.yearText.Text;

                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT CompID, SerialNo"
                            + " FROM [231_RobotOrderDetail]"
                            + " WHERE (LEFT(SerialNo, 2)='" + first_letter.Substring(0, 2) + "')"
                            + " AND (SUBSTRING(SerialNo, 3, 2) = '" + year_letter + "')"
                            + " ORDER BY SerialNo DESC";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            lastSerial.Text = dataReader["SerialNo"].ToString();
                        }
                        else
                        {
                            lastSerial.Text = first_letter + year_letter + month_letter + "00";
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        //error = "Cannot get Comp list";
                    }

                    SerialDigit = GetSerialDigit(lastSerial.Text, 4);
                }
            }

            // Calculate with Quantity
            int quantity = 0;
            Int32.TryParse(quantityValue.Text.Trim(), out quantity);
            
            if (this.compID.Text.Substring(0, 3) == "GHR")
            {
                this.lastSerial.Text = first_letter + GHR_year_letter + GetGHRSerialLetter(SerialDigit + quantity)
                    + ((SerialDigit + quantity) % 1000).ToString("D3");
                this.buttonAdd.Enabled = true;
            }
            else
            {
                string[] array1 = { "RT-AF", "RT-AR", "RT-BF", "RT-BR", "RT-CF", "RT-CR" };
                int NewSerialDigit;
                if (array1.Contains(first_letter))
                {
                    NewSerialDigit = SerialDigit + 1;
                    this.firstSerial.Text = first_letter + "-" + "1" + year_letter.Substring(year_letter.Length - 1, 1) + "-"
                        + month_letter + "-" + (NewSerialDigit % 100).ToString("D2");
                    NewSerialDigit = SerialDigit + quantity;
                    this.lastSerial.Text = first_letter + "-" + "1" + year_letter.Substring(year_letter.Length - 1, 1)
                        + "-" + month_letter + "-" + (NewSerialDigit % 100).ToString("D2");
                }
                else
                {
                    if (first_letter == "RT")
                    {
                        Dictionary<string, string> Month_Letters = new Dictionary<string, string>()
                                    {{"01", "1"}, {"02", "2"}, {"03", "3"}, {"04", "4"}, {"05", "5"}, {"06", "6"},
                                    {"07", "7"}, {"08", "8"}, {"09", "9"}, {"10", "X"}, {"11", "Y"}, {"12", "Z"}};
                        
                        if (Month_Letters.ContainsKey(this.monthText.Text))
                        {
                            month_letter = Month_Letters[this.monthText.Text];
                        }

                        year_letter = yearText.Text;


                        NewSerialDigit = SerialDigit + 1;
                        this.firstSerial.Text = first_letter + year_letter + month_letter + (NewSerialDigit % 100).ToString("D2");
                        NewSerialDigit = SerialDigit + quantity;
                        this.lastSerial.Text = first_letter + year_letter + month_letter + (NewSerialDigit % 100).ToString("D2");
                    }
                    else
                    {
                        NewSerialDigit = SerialDigit + 1;
                        SerialLetter = GetSerialLetter(NewSerialDigit);
                        this.firstSerial.Text = first_letter + year_letter + month_letter + SerialLetter
                            + (NewSerialDigit % 100).ToString("D2");
                        NewSerialDigit = SerialDigit + quantity;
                        SerialLetter = GetSerialLetter(NewSerialDigit);
                        this.lastSerial.Text = first_letter + year_letter + month_letter + SerialLetter + (NewSerialDigit % 100).ToString("D2");
                    }
                }
            }
        }

        ///////////////////////////////////////////
        /// Get GHR Serial Digit
        //////////////////////////////////////////
        private int GetGHRSerialDigit(string GHRSerial)
        {
            int ret = 0;
            
            if (GHRSerial.Length == 8)
            {
                if (GHRSerial.Substring(7, 1).ToUpper() == "F")
                {
                    GHRSerial = GHRSerial.Substring(0, 7);
                }
            }

            string temp = GHRSerial.Substring(3, 4);
            if (Int32.TryParse(temp, out ret))
            {
                return ret;
            }
            else
            {
                ret = (Array.IndexOf(Alphabet, temp.Substring(0, 1)) + 10) * 1000 + Int32.Parse(temp.Substring(1,3));
            } 
            return ret;
        }

        ///////////////////////////////////////////
        /// Get GHR Serial Letter
        //////////////////////////////////////////
        private string GetGHRSerialLetter(int SerialDigit)
        {
            string ret = "0";
            
            if (SerialDigit > 33000)
            {
                ret = "Z";
            }
            else
            {
                if (SerialDigit < 10000)
                {
                    ret = SerialDigit.ToString("D4").Substring(0, 1);
                }
                else
                {
                    int i = SerialDigit / 1000;
                    ret = Alphabet[i - 10];
                }
            } 
            return ret;
        }

        ///////////////////////////////////////////
        /// Get Serial Digit
        //////////////////////////////////////////
        private int GetSerialDigit(string last_Serial, int mode)
        {
            int ret = 0;            
            int i = 0;
            if ((mode == 2) || (mode == 3))
            {
                i = 6;
            }
            else
            {
                i = 5;
            }
            string str = last_Serial.Substring(i - 1, 1);

            Dictionary<string, int> dict = new Dictionary<string, int>()
                {{"A", 2000}, {"B", 2100}, {"C", 2200}, {"D", 2300}, {"E", 2400}, {"F", 2500}, {"G", 2600}, {"H", 2700},
                {"J", 2800}, {"K", 2900}, {"L", 3000}, {"M", 3100}, {"N", 3200}, {"P", 3300}, {"Q", 3400}, {"R", 3500},
                {"S", 3600}, {"T", 3700}, {"U", 3800}, {"V", 3900}, {"W", 4000}, {"X", 4100}, {"Y", 4200}, {"Z", 4300}};

            if (dict.ContainsKey(str))
            {
                ret = dict[str] + Int32.Parse(last_Serial.Substring(last_Serial.Length - 2, 2));
            }
            else
            {
                switch (mode)
                {
                    case 1:
                        ret = 1000 + Int32.Parse(last_Serial.Substring(4, 3));
                        break;
                    case 2:
                        ret = 1000 + Int32.Parse(last_Serial.Substring(5, 3));
                        break;
                    default:
                        ret = 1000 + Int32.Parse(last_Serial.Substring(last_Serial.Length - 2, 2));
                        break;
                }
            } 

            return ret;
        }

        ///////////////////////////////////////////
        /// Get Serial Letter
        ///////////////////////////////////////////
        private string GetSerialLetter(int digit)
        {

            string ret = null;             
            if (digit < 2000)
            {
                return digit.ToString("D4").Substring(1, 1);
            }

            if (digit < 2100)
            {
                return "A";
            }

            if (digit < 2200)
            {
                return "B";
            }

            if (digit < 2300)
            {
                return "C";
            }

            if (digit < 2400)
            {
                return "D";
            }

            if (digit < 2500)
            {
                return "E";
            }

            if (digit < 2600)
            {
                return "F";
            }

            if (digit < 2700)
            {
                return "G";
            }

            if (digit < 2800)
            {
                return "H";
            }

            if (digit < 2900)
            {
                return "J";
            }

            if (digit < 3000)
            {
                return "K";
            }

            if (digit < 3100)
            {
                return "L";
            }

            if (digit < 3200)
            {
                return "M";
            }

            if (digit < 3300)
            {
                return "N";
            }

            if (digit < 3400)
            {
                return "P";
            }

            if (digit < 3500)
            {
                return "Q";
            }

            if (digit < 3600)
            {
                return "R";
            }

            if (digit < 3700)
            {
                return "S";
            }

            if (digit < 3800)
            {
                return "T";
            }

            if (digit < 3900)
            {
                return "U";
            }

            if (digit < 4000)
            {
                return "V";
            }

            if (digit < 4100)
            {
                return "W";
            }

            if (digit < 4200)
            {
                return "X";
            }

            if (digit < 4300)
            {
                return "Y";
            }

            if (digit < 4400)
            {
                return "Z";
            } 

            return ret;
        }
                

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            InsertSerialNo();
        }

        private void InsertSerialNo()
        {
            string error = null;
            string order = null;
            string plan = null;
            string comp = null;
            int quantity = 0;
            DateTime deadline = new DateTime();            
            string customer = null;
            string remark = null;
            string first_serial = null;
            string last_serial = null;           

            ComboboxItem item = new ComboboxItem();

            Dictionary<string, string> Year_Letters = new Dictionary<string, string>()
                {{"A", "01"}, {"B", "02"}, {"C", "03"}, {"D", "04"}, {"E", "05"}, {"F", "06"}, {"G", "07"}, {"H", "08"},
                {"J", "09"}, {"K", "10"}, {"L", "11"}, {"M", "12"}, {"N", "13"}, {"O", "14"}, {"P", "15"}};


            if (string.IsNullOrEmpty(this.planNo.Text))
            {
                error = "The selected OrderNo have no Plan No";
            }
            else
            {
                plan = this.planNo.Text;
            }

            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(this.orderNo.Text))
                {
                    error = "Please enter Order No";
                }
                else
                {
                    order = this.orderNo.Text;
                }
            }
            
            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(this.compID.Text))
                {
                    error = "No CompID";
                }
                else
                {
                    comp = this.compID.Text;
                }
            }            

            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(this.quantityValue.Text))
                {
                    error = "Please enter Quantity";
                }
                else
                {
                    if (!Int32.TryParse(this.quantityValue.Text, out quantity))
                    {
                        error = "Invalid Quantity";
                    }
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.customerID.SelectedIndex >= 0)
                {
                    item = (ComboboxItem)this.customerID.SelectedItem;
                    customer = item.Value.ToString();
                }
                else
                {
                    error = "Please select Customer";
                }
            }

            
            if (string.IsNullOrEmpty(error))
            {
                if (this.deadLine.EditValue == null)
                {
                    error = "No DeadLine";
                }
                else
                {
                    deadline = DateTime.ParseExact(this.deadLine.Text, "yyyyMMdd", CultureInfo.InvariantCulture);
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                remark = this.remarkText.Text;
            }

            if (!this.noSerial.Checked)
            {
                if (string.IsNullOrEmpty(error))
                {
                    if (this.pbFree.Checked)
                    {
                        first_serial = this.firstSerial.Text + "f";
                        last_serial = this.lastSerial.Text + "f";
                    }
                    else
                    {
                        first_serial = this.firstSerial.Text;
                        last_serial = this.lastSerial.Text;
                    }
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                myConnection.Open();
                SqlCommand cmd_sql1;
                SqlCommand cmd_sql2;
                SqlTransaction tran = myConnection.BeginTransaction();
                string sql = null;
                try
                {
                    if (!this.noSerial.Checked)
                    {
                        if (comp.Substring(0, 3).ToUpper() == "GHR")
                        {
                            for (int i = 1; i <= quantity; i++)
                            {
                                string serial = first_letter + GHR_year_letter + GetGHRSerialLetter(SerialDigit + i)
                                    + ((SerialDigit + i) % 1000).ToString("D3") + (this.pbFree.Checked ? "f" : "");

                                //Insert into 231_RobotOrderDetail
                                //myConnection.Open();
                                sql = "INSERT INTO [231_RobotOrderDetail]"
                                    + " (SerialNo, PlanNo, OrderNo, CompID, Quantity, CustomerID, Remark)"
                                    + " VALUES ('" + serial + "', '" + plan + "', '" + order + "', '" + comp + "',"
                                    + " " + quantity + ", '" + customer + "', '" + remark + "')";
                                cmd_sql1 = new SqlCommand(sql, myConnection);
                                cmd_sql1.Transaction = tran;
                                cmd_sql1.ExecuteNonQuery();
                                //cmd_sql1.Dispose();
                                //myConnection.Close();                            

                            } // End for                    
                        }
                        else // Comp != GHR
                        {
                            if (!this.pbFree.Checked)
                            {
                                int NewSerialDigit = 0;
                                string[] firstletter_array = { "RT-AF", "RT-AR", "RT-BF", "RT-BR", "RT-CF", "RT-CR" };
                                string serial = null;

                                for (int i = 1; i <= quantity; i++)
                                {
                                    NewSerialDigit = SerialDigit + i;
                                    SerialLetter = GetSerialLetter(NewSerialDigit);

                                    if (firstletter_array.Contains(first_letter))
                                    {
                                        serial = first_letter + "-" + year_letter.Substring(year_letter.Length - 1, 1)
                                            + "-" + month_letter + "-" + (NewSerialDigit % 100).ToString("D2");
                                    }
                                    else
                                    {
                                        if (first_letter == "RT")
                                        {
                                            serial = first_letter + year_letter + month_letter + (NewSerialDigit % 100).ToString("D2");
                                        }
                                        else
                                        {
                                            serial = first_letter + year_letter + month_letter + SerialLetter + (NewSerialDigit % 100).ToString("D2");
                                        }
                                    }

                                    //Insert into 231_RobotOrderDetail

                                    //myConnection.Open();
                                    sql = "INSERT INTO [231_RobotOrderDetail]"
                                        + " (SerialNo, PlanNo, OrderNo, CompID, Quantity, CustomerID, Remark)"
                                        + " VALUES ('" + serial + "', '" + plan + "', '" + order + "', '" + comp + "',"
                                        + " " + quantity + ", '" + customer + "', '" + remark + "')";
                                    cmd_sql1 = new SqlCommand(sql, myConnection);
                                    cmd_sql1.Transaction = tran;
                                    cmd_sql1.ExecuteNonQuery();
                                    //cmd_sql.Dispose();
                                    //myConnection.Close();

                                } // End for                        
                            }
                            else // PBFree checked
                            {
                                int NewSerialDigit = 0;
                                string serial = null;

                                for (int i = 1; i <= quantity; i++)
                                {
                                    NewSerialDigit = SerialDigit + i;
                                    SerialLetter = GetSerialLetter(NewSerialDigit);

                                    serial = first_letter + year_letter + month_letter + SerialLetter
                                        + (NewSerialDigit % 100).ToString("D2") + "f";

                                    //Insert into 231_RobotOrderDetail
                                    //myConnection.Open();
                                    sql = "INSERT INTO [231_RobotOrderDetail]"
                                        + " (SerialNo, PlanNo, OrderNo, CompID, Quantity, CustomerID, Remark)"
                                        + " VALUES ('" + serial + "', '" + plan + "', '" + order + "', '" + comp + "',"
                                        + " " + quantity + ", '" + customer + "', '" + remark + "')";
                                    cmd_sql1 = new SqlCommand(sql, myConnection);
                                    cmd_sql1.Transaction = tran;
                                    cmd_sql1.ExecuteNonQuery();
                                    //cmd_sql.Dispose();
                                    //myConnection.Close();

                                } //End for                        
                            }
                        }
                    }

                    // Insert into 231_TimePlan

                    //myConnection.Open();
                    sql = "INSERT INTO [231_TimePlan]"
                        + " (TimePlanNo, PlanNo, CompID, Quantity, CustomerID, FromSerialNo, ToSerialNo, DeadLine, Remark)"
                        + " VALUES ('" + order + "', '" + plan + "', '" + comp + "', " + quantity + ","
                        + " '" + customer + "', '" + first_serial + "', '" + last_serial + "', @deadline, '" + remark + "')";
                    cmd_sql2 = new SqlCommand(sql, myConnection);
                    cmd_sql2.Transaction = tran;
                    cmd_sql2.Parameters.Add("@deadline", SqlDbType.DateTime).Value = deadline.Date;

                    cmd_sql2.ExecuteNonQuery();
                    //cmd_sql.Dispose();
                    //myConnection.Close();
                    tran.Commit();
                    MessageBox.Show("F4 Serial Input success.");
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    MessageBox.Show(ex.Message);                    
                }
                finally
                {                    
                    myConnection.Close();
                }

                this.buttonAdd.Enabled = false;
                
                DataSet sourceDataSet = new DataSet();
                SQLManagement mySQLManagement = new SQLManagement();
                mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
                string check_sql = "SELECT SerialNo, PlanNo, OrderNo, CompID"
                        + " FROM [231_RobotOrderDetail]"
                        + " WHERE SerialNo BETWEEN '" + first_serial + "' AND '" + last_serial + "'";
                sourceDataSet = mySQLManagement.SQL_Query(check_sql);
                if (sourceDataSet != null)
                {
                    gridNewSerial.DataSource = sourceDataSet.Tables[0];
                }
                else
                {
                    DataTable newTable = new DataTable();
                    newTable.Columns.Add("SerialNo", typeof(string));
                    newTable.Columns.Add("PlanNo", typeof(string));
                    newTable.Columns.Add("OrderNo", typeof(string));
                    newTable.Columns.Add("CompID", typeof(string));
                    gridNewSerial.DataSource = newTable;                    
                }
                NewSerial.Columns[0].Caption = "Serial No";
                NewSerial.Columns[1].Caption = "Plan No";
                NewSerial.Columns[2].Caption = "Order No.";
                NewSerial.Columns[3].Caption = "Comp ID";
                NewSerial.BestFitColumns();
            }
            else
            {
                MessageBox.Show(error);
                buttonAdd.Enabled = false;
            }            
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            CalculateSerialNo();
            this.buttonAdd.Enabled = true;
        }

        private void noSerial_CheckedChanged(object sender, EventArgs e)
        {
            if (this.noSerial.Checked)
            {
                this.firstSerial.Text = "";
                this.lastSerial.Text = "";
                this.buttonCalculate.Enabled = false;
                this.buttonAdd.Enabled = true;
            }
            else
            {
                this.buttonCalculate.Enabled = true;
                this.buttonAdd.Enabled = false;
            }
        }
    }
}
